package com.realtyManage.springboot.Utils;

import com.realtyManage.springboot.Model.Do.CustomerRegistration;
import com.realtyManage.springboot.Model.Do.HouseDetail;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;

import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Queue;

/**
 * Created with IDEA 2017
 * author:杭州素色网络科技有限公司
 * Date:2019/2/2
 * Time:下午 2:40
 */
@Component
public class ExcelUtil {
    private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);

    private static Integer startRow = 1;
    private static Integer startCol = 0;

    /**
     * 居中样式
     */
    private static HSSFCellStyle textAlignCenter;
    /**
     * 左对齐样式
     */
    private static HSSFCellStyle textAlignLeft;

    /**
     * @param datas         数据
     * @param exFieldNames 导出头名称
     * @param exFields     导出数据字段
     * @return  xls格式文件
     */
    public static <T> HSSFWorkbook exportExcel(List<HouseDetail> datas, String[] exFieldNames, String[] exFields,String fileName) {
        HSSFWorkbook workbook = null;
        try {
            if (null == exFieldNames || null == exFields || exFieldNames.length == 0 || exFields.length == 0 || exFieldNames.length != exFields.length) {
                throw new Exception("参数异常");
            }
            workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet(fileName);
            createSheetStyle(workbook, sheet, exFieldNames.length);

            HSSFRow row = sheet.createRow(0);
            HSSFCell cell;
            //设置表格中文栏样式
            HSSFCellStyle headStyle = workbook.createCellStyle();
            HSSFFont headcellFont = workbook.createFont();
            headcellFont.setFontHeightInPoints((short) 14);
            headcellFont.setColor(HSSFColor.BLACK.index);
            headcellFont.setFontName("Times New Roman");
            headcellFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            headStyle.setFont(headcellFont);
            headStyle.setAlignment(CellStyle.ALIGN_CENTER);
            headStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            headStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            headStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
            headStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
            for (int j = 0; j < exFieldNames.length; j++) {
                cell = row.createCell(j);
                cell.setCellValue(exFieldNames[j]);
                cell.setCellStyle(headStyle);
            }
            HSSFFont cellFont = workbook.createFont();
            cellFont.setFontHeightInPoints((short) 14);
            cellFont.setColor(HSSFColor.BLACK.index);
            cellFont.setFontName("Times New Roman");
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setFont(cellFont);
            cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
            cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
            cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
            for (int i = 0; i < datas.size(); i++) {
                HouseDetail t = datas.get(i);
                if (t.getType().equals("4")){
                    t.setType("成交");
                }else if (t.getType().equals("0")){
                    t.setType("在售");
                }else if (t.getType().equals("1")){
                    t.setType("认筹");
                }else if (t.getType().equals("2")){
                    t.setType("小订");
                }else if (t.getType().equals("3")){
                    t.setType("大定");
                }
                row = sheet.createRow(i + 1);
                for (int k = 0; k < exFields.length; k++) {
                    Object o = invokeGet(t, exFields[k]);
                    cell = row.createCell(k);
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    if (null != o && o instanceof Double && (Double) o == -1) {
                        cell.setCellValue("/");
                    } else if (null != o && o instanceof Double) {
                        // 取消科学计数法
                        cell.setCellValue((new BigDecimal(o.toString())).toString());
                    } else {
                        cell.setCellValue(o != null ? o.toString() : "");
                    }
                    cell.setCellStyle(cellStyle);
                }
            }
           // row = sheet.createRow(datas.size() + 2);
            /*for (int j = 1; j < exFields.length; j++) {
                if (j == 1) {
                    HSSFCell cell1 = row.createCell(j);
                    cell1.setCellValue("总计");
                    cell1.setCellType(Cell.CELL_TYPE_STRING);
                    cell1.setCellStyle(cellStyle);
                }else {
                    Double sum = new Double(0);
                    for (int i = 2; i < sheet.getLastRowNum(); i++) {
                        HSSFRow sheetRow = sheet.getRow(i);
                        HSSFCell cell1 = sheetRow.getCell(j);
                        String strValue = cell1.getStringCellValue();
                        *//*sum += Double.valueOf(strValue);*//*
                    }
                    HSSFCell cell1 = row.createCell(j);
                    cell1.setCellValue(sum);
                    cell1.setCellType(Cell.CELL_TYPE_STRING);
                    cell1.setCellStyle(cellStyle);
                }
            }*/
          //  sheet.autoSizeColumn(1);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return workbook;
    }


    public static <T> HSSFWorkbook exportCustomerExcel(List<CustomerRegistration> datas, String[] exFieldNames, String[] exFields, String fileName) {
        HSSFWorkbook workbook = null;
        try {
            if (null == exFieldNames || null == exFields || exFieldNames.length == 0 || exFields.length == 0 || exFieldNames.length != exFields.length) {
                throw new Exception("参数异常");
            }
            workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet(fileName);
            createSheetStyle(workbook, sheet, exFieldNames.length);

            HSSFRow row = sheet.createRow(0);
            HSSFCell cell;
            //设置表格中文栏样式
            HSSFCellStyle headStyle = workbook.createCellStyle();
            HSSFFont headcellFont = workbook.createFont();
            headcellFont.setFontHeightInPoints((short) 14);
            headcellFont.setColor(HSSFColor.BLACK.index);
            headcellFont.setFontName("Times New Roman");
            headcellFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            headStyle.setFont(headcellFont);
            headStyle.setAlignment(CellStyle.ALIGN_CENTER);
            headStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            headStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            headStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
            headStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
            for (int j = 0; j < exFieldNames.length; j++) {
                cell = row.createCell(j);
                cell.setCellValue(exFieldNames[j]);
                cell.setCellStyle(headStyle);
            }
            HSSFFont cellFont = workbook.createFont();
            cellFont.setFontHeightInPoints((short) 14);
            cellFont.setColor(HSSFColor.BLACK.index);
            cellFont.setFontName("Times New Roman");
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setFont(cellFont);
            cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
            cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
            cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
            for (int i = 0; i < datas.size(); i++) {
                CustomerRegistration t = datas.get(i);
                /*if (t.getType().equals("4")){
                    t.setType("成交");
                }else if (t.getType().equals("0")){
                    t.setType("在售");
                }else if (t.getType().equals("1")){
                    t.setType("认筹");
                }else if (t.getType().equals("2")){
                    t.setType("小订");
                }else if (t.getType().equals("3")){
                    t.setType("大定");
                }*/
                row = sheet.createRow(i + 1);
                for (int k = 0; k < exFields.length; k++) {
                    Object o = invokeGet(t, exFields[k]);
                    cell = row.createCell(k);
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    if (null != o && o instanceof Double && (Double) o == -1) {
                        cell.setCellValue("/");
                    } else if (null != o && o instanceof Double) {
                        // 取消科学计数法
                        cell.setCellValue((new BigDecimal(o.toString())).toString());
                    } else {
                        cell.setCellValue(o != null ? o.toString() : "");
                    }
                    cell.setCellStyle(cellStyle);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return workbook;
    }

    private static void createSheetStyle(HSSFWorkbook workbook, HSSFSheet sheet, Integer cols) {
        textAlignCenter = workbook.createCellStyle();
        textAlignLeft = workbook.createCellStyle();
        // 设置表字体
        HSSFFont font10 = workbook.createFont();
        font10.setFontHeightInPoints((short) 12);
        font10.setFontName("黑体");
        // 设置表样
        textAlignCenter.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        textAlignCenter.setWrapText(true);
        textAlignLeft.setAlignment(XSSFCellStyle.ALIGN_LEFT);
        textAlignCenter.setFont(font10);
        textAlignLeft.setFont(font10);
        // 设置列宽
        for (int i = 0; i < cols; i++) {
            sheet.setColumnWidth(i, 6000);
        }
        //设置自定换行
        textAlignLeft.setWrapText(true);
    }

    /**
     * 映射查找get方法
     *
     * @param o
     * @param fieldName
     * @return
     */
    public static Object invokeGet(Object o, String fieldName) {
        Method method = getGetMethod(o.getClass(), fieldName);
        try {
            return method.invoke(o, new Object[0]);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public static Method getGetMethod(Class objectClass, String fieldName) {
        StringBuilder sb = new StringBuilder();
        sb.append("get");
        sb.append(fieldName.substring(0, 1).toUpperCase());
        sb.append(fieldName.substring(1));
        try {
            return objectClass.getMethod(sb.toString());
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public static HSSFWorkbook exportDetail(Queue<String> queue){
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet hssfSheet = workbook.createSheet();
        // 设置缺省列高
        hssfSheet.setDefaultColumnWidth(22);
        hssfSheet.setDefaultRowHeightInPoints(22);
        Map<String, HSSFCellStyle> hssfCellStyleMap = createCellStyle(workbook);
        int rowNum = 0;
        int size = queue.size();
        if (size % 6 == 0) {
            rowNum = size / 6;
        } else {
            rowNum = size / 6 + 1;
        }
        rowNum += 3;
        log.info("rowNum得到的行数是:" + rowNum);
        //设置缺省列宽
        for (int i = 0; i < rowNum; i++) {
            HSSFRow row = hssfSheet.createRow(i);
            for (int j = 0; j < 6; j++) {
                HSSFCell rowCell = row.createCell(j);
                if (i == 0 || i == 1 || i == 5) {
                } else {
                    String str = queue.poll();
                    if (!StringUtils.isEmpty(str)) {
                    }
                    rowCell.setCellValue(str);
                }
            }
            if (i == rowNum-1) {
                hssfSheet.addMergedRegion(new CellRangeAddress(i, i, 1, 5));
                calcAndSetRowHeigt(row);
            }
            if (i == 0 || i == 1 || i == 5) {
                hssfSheet.addMergedRegion(new CellRangeAddress(i, i, 0, 5));
                HSSFCell rowCell = hssfSheet.getRow(i).getCell(0);
                if (i == 0) {
                    rowCell.setCellValue("结算配置详情");
                }
                if (i == 1) {
                    rowCell.setCellValue("报表信息");
                }
                if (i == 5) {
                    rowCell.setCellValue("SP/CP/SI配置信息");
                }
            }
        }
        for (int i = 0; i < rowNum; i++) {
            HSSFRow row = hssfSheet.getRow(i);
            int enterCnt = 0;
            for (int j = 0; j < row.getLastCellNum(); j++) {
                HSSFCell rowCell = row.getCell(j);
                if (i == 0 || i == 1 || i == 5) {
                    rowCell.setCellStyle(hssfCellStyleMap.get("titleStyle"));
                } else {
                    if (j % 2 == 0) {
                        rowCell.setCellStyle(hssfCellStyleMap.get("headStyle"));
                    } else {
                        rowCell.setCellStyle(hssfCellStyleMap.get("contentStyle"));
                        String str = rowCell == null ? "" : rowCell.toString();
                        //当前单元格宽度5632 大概放7个字符填满
                        if (!StringUtils.isEmpty(str)) {
                            int length = 0;
                            if (str.length() % 10 == 0) {
                                length = str.length();
                            } else {
                                length = str.length() + 1;
                            }
                            if (length > enterCnt) {
                                enterCnt = length;
                            }
                        }
                    }
                }
            }
            if (enterCnt == 0) {
                enterCnt = 1;
            }
        }
        return workbook;

    }

    public static Map<String, HSSFCellStyle> createCellStyle(HSSFWorkbook workbook){

        Map<String, HSSFCellStyle> styleMap = new HashMap<String, HSSFCellStyle>();
        //标题格式
        HSSFFont titleFont = workbook.createFont();
        titleFont.setFontHeightInPoints((short) 14);
        titleFont.setColor(HSSFColor.BLACK.index);
        titleFont.setFontName("Times New Roman");
        HSSFCellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        titleStyle.setFont(titleFont);
        titleStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        styleMap.put("titleStyle", titleStyle);

        //内容样式
        HSSFFont cellFont = workbook.createFont();
        cellFont.setFontHeightInPoints((short) 14);
        cellFont.setColor(HSSFColor.BLACK.index);
        cellFont.setFontName("Times New Roman");
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(CellStyle.ALIGN_LEFT);
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setWrapText(true);
        cellStyle.setFont(cellFont);
        styleMap.put("contentStyle", cellStyle);

        HSSFFont headFont = workbook.createFont();
        headFont.setFontHeightInPoints((short) 14);
        headFont.setColor(HSSFColor.BLACK.index);
        headFont.setFontName("Times New Roman");
        HSSFCellStyle headStyle = workbook.createCellStyle();
        headStyle.setAlignment(CellStyle.ALIGN_LEFT);
        headStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        headStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        headStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        headStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        headStyle.setFont(headFont);
        headStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        headStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        styleMap.put("headStyle", headStyle);
        return styleMap;
    }

    public static void calcAndSetRowHeigt(HSSFRow sourceRow) {
        for (int cellIndex = sourceRow.getFirstCellNum(); cellIndex <= sourceRow.getPhysicalNumberOfCells(); cellIndex++) {
            //行高
            double maxHeight = sourceRow.getHeight();
            HSSFCell sourceCell = sourceRow.getCell(cellIndex);
            //单元格的内容
            String cellContent = getCellContentAsString(sourceCell);
            if (null == cellContent || "".equals(cellContent)) {
                continue;
            }
            //单元格的宽高及单元格信息
            Map<String, Object> cellInfoMap = getCellInfo(sourceCell);
            Integer cellWidth = (Integer) cellInfoMap.get("width");
            Integer cellHeight = (Integer) cellInfoMap.get("height");
            if (cellHeight > maxHeight) {
                maxHeight = cellHeight;
            }
            System.out.println("单元格的宽度 : " + cellWidth + "    单元格的高度 : " + maxHeight + ",    单元格的内容 : " + cellContent);
            HSSFCellStyle cellStyle = sourceCell.getCellStyle();
            HSSFFont font = cellStyle.getFont(sourceRow.getSheet().getWorkbook());
            //字体的高度
            short fontHeight = font.getFontHeight();

            //cell内容字符串总宽度
            double cellContentWidth = cellContent.getBytes().length * 2 * 256;

            //字符串需要的行数 不做四舍五入之类的操作
            double stringNeedsRows = (double) cellContentWidth / cellWidth;
            //小于一行补足一行
            if (stringNeedsRows < 1.0) {
                stringNeedsRows = 1.0;
            }
            //需要的高度(Math.floor(stringNeedsRows) - 1) * 40 为两行之间空白高度
            double stringNeedsHeight = (double) fontHeight * stringNeedsRows;
            //需要重设行高
            if (stringNeedsHeight > maxHeight) {
                maxHeight = stringNeedsHeight;
                //超过原行高三倍 则为5倍 实际应用中可做参数配置
                if (maxHeight / cellHeight > 5) {
                    maxHeight = 5 * cellHeight;
                }
                //最后取天花板防止高度不够
                maxHeight = Math.ceil(maxHeight);
                //重新设置行高 同时处理多行合并单元格的情况
                Boolean isPartOfRowsRegion = (Boolean) cellInfoMap.get("isPartOfRowsRegion");
                if (isPartOfRowsRegion) {
                    Integer firstRow = (Integer) cellInfoMap.get("firstRow");
                    Integer lastRow = (Integer) cellInfoMap.get("lastRow");
                    //平均每行需要增加的行高
                    double addHeight = (maxHeight - cellHeight) / (lastRow - firstRow + 1);
                    for (int i = firstRow; i <= lastRow; i++) {
                        double rowsRegionHeight = sourceRow.getSheet().getRow(i).getHeight() + addHeight;
                        sourceRow.getSheet().getRow(i).setHeight((short) rowsRegionHeight);
                    }
                } else {
                    sourceRow.setHeight((short) maxHeight);
                }
            }
            System.out.println("字体高度 : " + fontHeight + ",    字符串宽度 : " + cellContentWidth + ",    字符串需要的行数 : " + stringNeedsRows + ",   需要的高度 : " + stringNeedsHeight + ",   现在的行高 : " + maxHeight);
            System.out.println();
        }
    }

    /**
     * 解析一个单元格得到数据
     *
     * @param cell
     * @return
     */
    private static String getCellContentAsString(HSSFCell cell) {
        if (null == cell) {
            return "";
        }
        String result = "";
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                String s = String.valueOf(cell.getNumericCellValue());
                if (s != null) {
                    if (s.endsWith(".0")) {
                        s = s.substring(0, s.length() - 2);
                    }
                }
                result = s;
                break;
            case Cell.CELL_TYPE_STRING:
                String ss = String.valueOf(cell.getStringCellValue());
                result = ss == null ? "" : ss.trim();
                break;
            case Cell.CELL_TYPE_BLANK:
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                result = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_ERROR:
                break;
            default:
                break;
        }
        return result;
    }

    /**
     * 获取单元格及合并单元格的宽度
     *
     * @param cell
     * @return
     */
    private static Map<String, Object> getCellInfo(HSSFCell cell) {
        HSSFSheet sheet = cell.getSheet();
        int rowIndex = cell.getRowIndex();
        int columnIndex = cell.getColumnIndex();

        boolean isPartOfRegion = false;
        int firstColumn = 0;
        int lastColumn = 0;
        int firstRow = 0;
        int lastRow = 0;
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            Region ca = sheet.getMergedRegionAt(i);
            firstColumn = ca.getColumnFrom();
            lastColumn = ca.getColumnTo();
            firstRow = ca.getRowFrom();
            lastRow = ca.getRowTo();
            if (rowIndex >= firstRow && rowIndex <= lastRow) {
                if (columnIndex >= firstColumn && columnIndex <= lastColumn) {
                    isPartOfRegion = true;
                    break;
                }
            }
        }
        Map<String, Object> map = new HashMap<String, Object>();
        Integer width = 0;
        Integer height = 0;
        boolean isPartOfRowsRegion = false;
        if (isPartOfRegion) {
            for (int i = firstColumn; i <= lastColumn; i++) {
                width += sheet.getColumnWidth(i);
            }
            for (int i = firstRow; i <= lastRow; i++) {
                height += sheet.getRow(i).getHeight();
            }
            if (lastRow > firstRow) {
                isPartOfRowsRegion = true;
            }
        } else {
            width = sheet.getColumnWidth(columnIndex);
            height += cell.getRow().getHeight();
        }
        map.put("isPartOfRowsRegion", isPartOfRowsRegion);
        map.put("firstRow", firstRow);
        map.put("lastRow", lastRow);
        map.put("width", width);
        map.put("height", height);
        return map;
    }

    public static Integer getStartRow() {
        return startRow;
    }

    public static void setStartRow(Integer startRow) {
        ExcelUtil.startRow = startRow;
    }

    public static Integer getStartCol() {
        return startCol;
    }

    public static void setStartCol(Integer startCol) {
        ExcelUtil.startCol = startCol;
    }

    public static HSSFCellStyle getTextAlignCenter() {
        return textAlignCenter;
    }

    public static void setTextAlignCenter(HSSFCellStyle textAlignCenter) {
        ExcelUtil.textAlignCenter = textAlignCenter;
    }

    public static HSSFCellStyle getTextAlignLeft() {
        return textAlignLeft;
    }

    public static void setTextAlignLeft(HSSFCellStyle textAlignLeft) {
        ExcelUtil.textAlignLeft = textAlignLeft;
    }
}

